-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		LE THANH SON
-- Create date: 14/03/2010
-- Description:	Store Procedures for InHD Project
-- =============================================
USE inhd
GO
-- GetAll
CREATE PROCEDURE GetAllAcc (@RowCount int output)  
as 

select * from AccMaster where archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllAccFull (@RowCount int output)  
as 

select * from AccMaster

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllCategory (@RowCount int output)  
as 

select * from CategoryMaster where archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllCategoryFull (@RowCount int output)  
as 

select * from CategoryMaster

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllCompany (@RowCount int output)  
as 

select * from CompanyInfo where archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllCompanyFull (@RowCount int output)  
as 

select * from CompanyInfo

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO


CREATE PROCEDURE GetAllCustomer (@RowCount int output)  
as 

select * from CustomerMaster where archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllCustomerFull (@RowCount int output)  
as 

select * from CustomerMaster

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllInvoice(@RowCount int output)  
as 

select * from InvoiceTempl where archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllInvoiceFull (@RowCount int output)  
as 

select * from InvoiceTempl

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllMngUnit (@RowCount int output)  
as 

select * from MngUnit where archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllMngUnitFull (@RowCount int output)  
as 

select * from MngUnit

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllPackage (@RowCount int output)  
as 

select * from PackageMaster where archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllPackageFull (@RowCount int output)  
as 

select * from PackageMaster where archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllProduct (@RowCount int output)  
as 

select * from ProductMaster where archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllProductFull (@RowCount int output)  
as 

select * from ProductMaster

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllProductInRange (@start int,
										@range int,
										@RowCount int output)  
as 

select * from ProductMaster
where	id >= @start and id < (@start + @range) and  archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllProductInRangeFull (@start int,
										@range int,
										@RowCount int output)  
as 

select * from ProductMaster
where	id >= @start and id < (@start + @range)

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllReg (@RowCount int output)  
as 

select * from RegMaster where archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllRegFull (@RowCount int output)  
as 

select * from RegMaster

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllStaff (@RowCount int output)  
as 

select * from StaffMaster where archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllStaffFull (@RowCount int output)  
as 

select * from StaffMaster

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllSupplier (@RowCount int output)  
as 

select * from SupplierMaster where archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllSupplierFull (@RowCount int output)  
as 

select * from SupplierMaster

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO


CREATE PROCEDURE GetAllTax(@RowCount int output)  
as 

select * from TaxMaster where archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllTaxFull (@RowCount int output)  
as 

select * from TaxMaster

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllTransaction (@RowCount int output)  
as 

select * from TransactionMaster where archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllTransactionFull (@RowCount int output)  
as 

select * from TransactionMaster

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO


-- ADDING PROCEDURES

CREATE PROCEDURE AddCompanyInfo(
							@name nvarchar(255),
							@address nvarchar(1000),
							@phone varchar(255),
							@email varchar(255),
							@director nvarchar(255),
							@taxCode varchar(255))
AS

INSERT INTO CompanyInfo VALUES (@name, @address, @phone, @email, @director, @taxCode, '')
GO

CREATE PROCEDURE AddCategory(@categoryUNK varchar(36),
							@name nvarchar(255),
							@description nvarchar(1000))
AS

INSERT INTO CategoryMaster VALUES (@categoryUNK, @name, @description, '')
GO

CREATE PROCEDURE AddProduct (@productUNK varchar(36),
							@proDes nvarchar(1000),
							@quantity int,
							@retailPrc real,
							@salePrc real,
							@importPrice real,
							@proName nvarchar(255),
							@categoryUNK varchar(36),
							@categoryName nvarchar(255),
							@sn varchar(10),
							@supplierUNK varchar(36),
							@lastAccess datetime)
AS

INSERT INTO ProductMaster VALUES (@productUNK, @proDes, @quantity, @retailPrc, @salePrc, @importPrice, @proName, @categoryUNK, @categoryName, @sn, @supplierUNK, @lastAccess, '')
GO

CREATE PROCEDURE AddCustomer (@customerUNK varchar(36),
								@customerCode varchar(36),
								@firstname nvarchar(255),
								@lastname nvarchar(255),
								@address nvarchar(1000),
								@email varchar(255),
								@phone varchar(10),
								@fax varchar(20),
								@totalBought real,
								@company nvarchar(255),
								@position nvarchar(255),
								@lastAccess datetime
								)
AS

INSERT INTO CustomerMaster VALUES (@customerUNK, @customerCode, @firstname, @lastname, @address, @email, @phone, @fax, @totalBought, @company, @position, @lastAccess, '')
GO

CREATE PROCEDURE AddPackage (@packageUNK varchar(36),
								@quantity int,
								@salePrice real,
								@transactionUNK varchar(36),
								@productUNK varchar(36),
								@productCode varchar(10),
								@productName varchar(255),
								@multiply int
								)
AS

INSERT INTO PackageMaster VALUES (@packageUNK, @quantity, @salePrice, @transactionUNK, @productUNK, @productCode, @productName, '')
UPDATE ProductMaster SET quantityOnStore = quantityOnStore - @multiply * @quantity
WHERE productUNK = @productUNK

GO

CREATE PROCEDURE AddReg (@regUNK varchar(36),
							@modified datetime,
							@modifyBy int,
							@regDate datetime,
							@invoiceUNK varchar(36),
							@status varchar(10),
							@receiverUNK varchar(36),
							@startDate datetime,
							@expireDate datetime,
							@number int
								)
AS

INSERT INTO RegMaster VALUES (@regUNK, @modified, @modifyBy, @regDate, @invoiceUNK, @status, @receiverUNK, @startDate, @expireDate, @number, @number, '')
GO

CREATE PROCEDURE AddStaff (@staffUNK varchar(36),
							@staffCode varchar(255),
							@name nvarchar(255),
							@address nvarchar(1000),
							@phone varchar(255),
							@email varchar(255),
							@lastAccess datetime,
							@staffType int,
							@userName varchar(255),
							@passWord varchar(255)
								)
AS

INSERT INTO StaffMaster VALUES (@staffUNK, @staffCode, @name, @address, @phone, @email, @lastAccess, @staffType, @userName, @passWord, '')
GO

CREATE PROCEDURE AddSupplier (@supplierUNK varchar(36),
								@name nvarchar(255),
							    @contact nvarchar(255), 
								@address nvarchar(1000),
								@email varchar(255),
								@phone varchar(10),
								@fax varchar(20),
								@supplierCode varchar(255),
								@deliverDelay int,
								@freeDelivery int
								)
AS

INSERT INTO SupplierMaster VALUES (@supplierUNK, @name, @contact, @address, @email, @phone, @fax, @supplierCode, @deliverDelay, @freeDelivery, '')
GO

CREATE PROCEDURE AddInvoiceTempl (@invoiceUNK varchar(36),
								@content varbinary(MAX),
							    @modifyBy int, 
								@modifyDate datetime,
								@name nvarchar(255),
								@code varchar(255),
								@tranType varchar(10)
								)
AS

INSERT INTO InvoiceTempl VALUES (@invoiceUNK, @content, @modifyBy, @modifyDate, @name, @code, @tranType, '')
GO

CREATE PROCEDURE AddTransaction (@transactionUNK varchar(36),
								@modified datetime,
								@taxUNK varchar(36),
								@discountAmt real,
								@totalSaleAmt real,
								@invoiceUNK varchar(36),
								@transType int,
								@paymentType int,
								@accUNK varchar(36),
								@cusUNK varchar(36),
								@staffUNK varchar(36),
								@status int,
								@customerFName nvarchar(255),
								@customerLName nvarchar(255),
								@taxName nvarchar(255),
								@taxPercentage real,
								@archiveFlag varchar(36)
								)
AS

INSERT INTO TransactionMaster VALUES (@transactionUNK, @modified, @taxUNK, @discountAmt, @totalSaleAmt, @invoiceUNK, @transType, @paymentType, @accUNK, @cusUNK, @staffUNK, @status, @customerFName, @customerLName, @taxName, @taxPercentage, @archiveFlag)
GO




-- SELECT PROCEDURES

CREATE PROCEDURE GetProductByCategory (@categoryUNK varchar(36), @RowCount int output)  
AS 

SELECT 
	ProductMaster.productUNK, 
	ProductMaster.proName, 
	ProductMaster.proDescription, 
	ProductMaster.quantityOnStore, 
	ProductMaster.retailUnitPrice,
	ProductMaster.wholesaleUnitPrice,
	ProductMaster.importPrice,
	ProductMaster.categoryUNK,
	ProductMaster.categoryName,
	SupplierMaster.name,
	SupplierMaster.supplierUNK,	
	ProductMaster.sn,
	ProductMaster.lastAccess,
	ProductMaster.importPrice
FROM 
	ProductMaster, 
	SupplierMaster
WHERE 
	ProductMaster.categoryUNK like @categoryUNK and 
	ProductMaster.categoryUNK like @categoryUNK and
	ProductMaster.supplierUNK like SupplierMaster.supplierUNK and
	ProductMaster.archiveFlag like '' and
	SupplierMaster.archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetProductBySupplierEx (@supplierUNK varchar(36), @RowCount int output)  
AS 

SELECT 
	ProductMaster.*,
	CategoryMaster.name
FROM 
	ProductMaster, 
	CategoryMaster, 
	SupplierMaster
WHERE 
	ProductMaster.supplierUNK like SupplierMaster.supplierUNK and
	CategoryMaster.categoryUNK like ProductMaster.categoryUNK and
	ProductMaster.supplierUNK like @supplierUNK and
	ProductMaster.archiveFlag like '' and
	CategoryMaster.archiveFlag like '' and
	SupplierMaster.archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllProductByCategory (@RowCount int output)  
AS 

SELECT 
	ProductMaster.productUNK, 
	ProductMaster.proName, 
	ProductMaster.proDescription, 
	ProductMaster.quantityOnStore, 
	ProductMaster.retailUnitPrice,
	ProductMaster.wholesaleUnitPrice,
	SupplierMaster.name,
	SupplierMaster.supplierUNK,
	ProductMaster.categoryUNK,
	ProductMaster.categoryName,
	ProductMaster.sn,
	ProductMaster.lastAccess,
	ProductMaster.importPrice
FROM 
	ProductMaster, 
	SupplierMaster
WHERE 
	ProductMaster.supplierUNK like SupplierMaster.supplierUNK and
	ProductMaster.archiveFlag like '' and
	SupplierMaster.archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetAllProductBySupplierEx (@RowCount int output)  
AS 

SELECT 
	ProductMaster.*,
	CategoryMaster.name
FROM 
	ProductMaster, 
	CategoryMaster, 
	SupplierMaster
WHERE 
	ProductMaster.supplierUNK like SupplierMaster.supplierUNK and
	ProductMaster.categoryUNK like CategoryMaster.categoryUNK and
	ProductMaster.archiveFlag like '' and
	CategoryMaster.archiveFlag like '' and
	SupplierMaster.archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetProductBySupplier (@supplierUNK varchar(36), @RowCount int output)  
AS 

SELECT 
	ProductMaster.sn,  
	ProductMaster.proDescription,
	ProductMaster.lastAccess,
	ProductMaster.importPrice, 
	ProductMaster.quantityOnStore 
FROM 
	ProductMaster
WHERE 
	ProductMaster.supplierUNK like @supplierUNK and
	ProductMaster.archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetProductBySupplierAndCategory (@supplierUNK varchar(36), @categoryUNK varchar(36), @RowCount int output)  
AS 

SELECT 
	ProductMaster.productUNK, 
	ProductMaster.proName, 
	ProductMaster.proDescription, 
	ProductMaster.quantityOnStore, 
	ProductMaster.retailUnitPrice,
	ProductMaster.wholesaleUnitPrice,
	ProductMaster.categoryName,
	SupplierMaster.name,
	SupplierMaster.supplierUNK,
	ProductMaster.categoryUNK
FROM 
	ProductMaster, 
	SupplierMaster
WHERE 
	ProductMaster.categoryUNK like @categoryUNK and 
	ProductMaster.categoryUNK like @categoryUNK and
	SupplierMaster.supplierUNK like @supplierUNK and 
	ProductMaster.supplierUNK like @supplierUNK and
	ProductMaster.supplierUNK like SupplierMaster.supplierUNK and
	ProductMaster.archiveFlag like '' and
	SupplierMaster.archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE UpdateProduct (@productUNK varchar(36), 
								@proName nvarchar(255), 
								@proDescription nvarchar(1000), 
								@quantityOnStore int, 
								@retailUnitPrice real,
								@wholeSaleUnitPrice real,
								@supplierUNK varchar(36),
								@categoryUNK varchar(36))
AS
UPDATE
	ProductMaster
SET
	proName = @proName,
	proDescription = @proDescription,
	quantityOnStore = @quantityOnStore,
	retailUnitPrice = @retailUnitPrice,
	wholesaleUnitPrice = @wholeSaleUnitPrice,
	supplierUNK = @supplierUNK,
	categoryUNK = @categoryUNK
WHERE
	productUNK like @productUNK and
	archiveFlag like ''
GO

CREATE PROCEDURE GetProductsOfSupplier (@supplierUNK varchar(36), @RowCount int output)  
AS 

SELECT 
	ProductMaster.*
FROM 
	ProductMaster
WHERE 
	ProductMaster.supplierUNK like @supplierUNK and
	archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO
	
CREATE PROCEDURE ReceiveProduct (@productUNK varchar(36), @quantity int, @importPrice real) 
AS

UPDATE 
	ProductMaster
SET
	quantityOnStore = quantityOnStore + @quantity,
	importPrice = @importPrice
WHERE 
	productUNK like @productUNK and
	archiveFlag like ''
GO

CREATE PROCEDURE GetTransactionByStaff (@staffUNK varchar(36), @RowCount int output)  
AS 

SELECT 
	TransactionMaster.modified,
	TransactionMaster.transactionUNK,
	TransactionMaster.totalSaleAmt
FROM 
	TransactionMaster
WHERE 
	TransactionMaster.staffUNK like @staffUNK and
	archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetTransactionByCustomer (@cusUNK varchar(36), @RowCount int output)  
AS 

SELECT 
	*
FROM 
	TransactionMaster
WHERE 
	TransactionMaster.cusUNK like @cusUNK and
	archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetTransactionByCustomerFull (@cusUNK varchar(36), @RowCount int output)  
AS 

SELECT 
	*
FROM 
	TransactionMaster
WHERE 
	TransactionMaster.cusUNK like @cusUNK

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetPurchaseProductsByCustomer (@cusUNK varchar(36), @RowCount int output)  
AS 

SELECT 
	TransactionMaster.modified,
	ProductMaster.proName,
	PackageMaster.quantity,
	PackageMaster.salePrice
FROM 
	TransactionMaster,
	PackageMaster,
	ProductMaster
WHERE 
	TransactionMaster.cusUNK like @cusUNK and
	TransactionMaster.transactionUNK like PackageMaster.transactionUNK and
	ProductMaster.productUNK like PackageMaster.productUNK and
	TransactionMaster.archiveFlag like '' and
	PackageMaster.archiveFlag like '' and
	ProductMaster.archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetReceivedBySupplier (@supUNK varchar(36), @RowCount int output)  
AS 

SELECT 
	TransactionMaster.modified,
	StaffMaster.name,
	TransactionMaster.totalSaleAmt
FROM 
	TransactionMaster,
	ProductMaster,
	StaffMaster,
	PackageMaster
WHERE 
	TransactionMaster.transactionUNK like PackageMaster.transactionUNK and
	PackageMaster.productUNK like ProductMaster.productUNK and
	ProductMaster.supplierUNK like @supUNK and
	TransactionMaster.staffUNK like StaffMaster.staffUNK and
	TransactionMaster.transType like 1 and
	TransactionMaster.archiveFlag like '' and
	ProductMaster.archiveFlag like '' and
	StaffMaster.archiveFlag like '' and
	PackageMaster.archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetReturnedBySupplier (@supUNK varchar(36), @RowCount int output)  
AS 

SELECT 
	TransactionMaster.modified,
	StaffMaster.name,
	TransactionMaster.totalSaleAmt
FROM 
	TransactionMaster,
	ProductMaster,
	StaffMaster,
	PackageMaster
WHERE 
	TransactionMaster.transactionUNK like PackageMaster.transactionUNK and
	PackageMaster.productUNK like ProductMaster.productUNK and
	ProductMaster.supplierUNK like @supUNK and
	TransactionMaster.staffUNK like StaffMaster.staffUNK and
	TransactionMaster.transType like 1 and
	TransactionMaster.archiveFlag like '' and
	ProductMaster.archiveFlag like '' and
	StaffMaster.archiveFlag like '' and
	PackageMaster.archiveFlag like ''

/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
select @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetPackgesByTransaction (@transactionUNK varchar(36), @RowCount int output)  
AS 

SELECT * 
FROM PackageMaster
WHERE transactionUNK like @transactionUNK and
	archiveFlag like ''
/* @@ROWCOUNT returns the number of rows that are affected by the last statement. */ 
SELECT @RowCount=@@ROWCOUNT
GO

CREATE PROCEDURE GetInvoiceContent (@InvoiceTmplUNK varchar(36), @RowCount int output)
AS

SELECT content
FROM InvoiceTempl
WHERE invoiceUNK like @InvoiceTmplUNK and
		archiveFlag like ''

GO
-- UPDATE PROCEDURES

CREATE PROCEDURE UpdateCategory (@categoryUNK varchar(36), 
									@name nvarchar(255), 
									@description nvarchar(1000)) 
AS

UPDATE 
	CategoryMaster
SET
	name = @name,
	catDescrp = @description
WHERE 
	categoryUNK like @categoryUNK and 
	archiveFlag like ''
GO

CREATE PROCEDURE UpdateCustomer (@customerUNK varchar(36),
								@customerCode varchar(36),
								@firstname nvarchar(255),
								@lastname nvarchar(255),
								@address nvarchar(1000),
								@email varchar(255),
								@phone varchar(10),
								@fax varchar(20),
								@totalBought real,
								@company nvarchar(255),
								@position nvarchar(255),
								@lastAccess datetime
								)
AS

UPDATE 
	CustomerMaster
SET
	customerCode = @customerCode,
	firstName = @firstname,
	lastName = @lastname,
	[address] = @address,
	email = @email,
	phone =@phone,
	fax = @fax,
	totalBoughtAmt = @totalBought,
	company = @company,
	position = @position,
	lastAccess = @lastAccess
WHERE 
	customerUNK like @customerUNK and 
	archiveFlag like ''
GO

CREATE PROCEDURE UpdatePackage (@packageUNK varchar(36),
								@quantity int,
								@salePrice real,
								@transactionUNK varchar(36),
								@productUNK varchar(36)
								)
AS

UPDATE 
	PackageMaster
SET
	quantity = @quantity,
	salePrice = @salePrice,
	transactionUNK = @transactionUNK,
	productUNK = @productUNK
WHERE 
	packageUNK like @packageUNK and 
	archiveFlag like ''
GO

CREATE PROCEDURE UpdateProductEx (@productUNK varchar(36),
							@proDes nvarchar(1000),
							@quantity int,
							@retailPrc real,
							@salePrc real,
							@importPrice real,
							@proName nvarchar(255),
							@categoryUNK varchar(36),
							@categoryName nvarchar(255),
							@sn varchar(10),
							@supplierUNK varchar(36),
							@lastAccess datetime)
AS

UPDATE 
	ProductMaster
SET
	proDescription = @proDes,
	quantityOnStore = @quantity,
	retailUnitPrice = @retailPrc,
	wholesaleUnitPrice = @salePrc,
	importPrice = @importPrice,
	proName = @proName,
	categoryUNK = @categoryUNK,
	categoryName = @categoryName,
	sn = @sn,
	supplierUNK = @supplierUNK,
	lastAccess = @lastAccess
WHERE 
	productUNK like @productUNK and 
	archiveFlag like ''
GO

CREATE PROCEDURE UpdateSupplier (@supplierUNK varchar(36),
								@name nvarchar(255),
							    @contact nvarchar(255), 
								@address nvarchar(1000),
								@email varchar(255),
								@phone varchar(10),
								@fax varchar(20),
								@supplierCode varchar(255),
								@deliverDelay int,
								@freeDelivery int
								)
AS

UPDATE 
	SupplierMaster
SET
	name = @name,
	contact = @contact,
	[address] = @address,
	email = @email,
	phone = @phone,
	fax = @fax,
	supplierCode = @supplierCode,
	deliverDelay = @deliverDelay,
	freeDelivery = @freeDelivery
WHERE 
	supplierUNK like @supplierUNK and 
	archiveFlag like ''
GO

CREATE PROCEDURE UpdateCompany(
							@name nvarchar(255),
							@address nvarchar(1000),
							@phone varchar(255),
							@email varchar(255),
							@director nvarchar(255),
							@taxCode varchar(255))
AS

UPDATE
	CompanyInfo
SET
	name = @name,
	address = @address,
	phone = @phone,
	email = @email,
	director = @director,
	taxCode = @taxCode
WHERE
	archiveFlag like ''
GO

CREATE PROCEDURE UpdateStaff (@staffUNK varchar(36),
							@staffCode varchar(255),
							@name nvarchar(255),
							@address nvarchar(1000),
							@phone varchar(255),
							@email varchar(255),
							@lastAccess datetime,
							@staffType int,
							@userName varchar(255),
							@passWord varchar(255)
								)
AS

UPDATE
	StaffMaster
SET
	staffCode = @staffCode,
	name = @name,
	address = @address,
	phone = @phone,
	email = @email,
	lastAccess = @lastAccess,
	staffType = @staffType,
	userName = @userName,
	passWord = @passWord
WHERE
	staffUNK like @staffUNK and
	archiveFlag like ''
GO

CREATE PROCEDURE UpdateTransaction (@transactionUNK varchar(36),
								@modified datetime,
								@taxUNK varchar(36),
								@discountAmt real,
								@totalSaleAmt real,
								@invoiceUNK varchar(36),
								@transType int,
								@paymentType int,
								@accUNK varchar(36),
								@cusUNK varchar(36),
								@staffUNK varchar(36),
								@status int,
								@customerFName varchar(255),
								@customerLName varchar(255),
								@taxName varchar(255),
								@taxPercentage real
								)
AS

UPDATE
	TransactionMaster
SET
	modified = @modified,
	taxUNK = @taxUNK,
	discountAmt = @discountAmt,
	totalSaleAmt = @totalSaleAmt,
	invTemplUNK = @invoiceUNK,
	transType = @transType,
	paymentType = @paymentType,
	accUNK = @accUNK,
	cusUNK = @cusUNK,
	staffUNK = @staffUNK,
	status = @status,
	customerFName = @customerFName,
	customerLName = @customerLName,
	taxName = @taxName,
	taxPercentage = @taxPercentage
WHERE
	transactionUNK like @transactionUNK and
	archiveFlag like ''
GO

--DELETE PROCEDURES

CREATE PROCEDURE DeleteAcc (@accUNK varchar(36))
AS

UPDATE
	AccMaster
SET
	archiveFlag = @accUNK
WHERE
	accUNK like @accUNK
GO
	
CREATE PROCEDURE DeleteCategory (@categoryUNK varchar(36))
AS

UPDATE
	CategoryMaster
SET
	archiveFlag = @categoryUNK
WHERE
	categoryUNK like @categoryUNK
GO

CREATE PROCEDURE DeleteCompany (@companyName varchar(255))
AS

UPDATE
	CompanyInfo
SET
	archiveFlag = @companyName
WHERE
	name like @companyName
GO

CREATE PROCEDURE DeleteCustomer (@cusUNK varchar(36))
AS

UPDATE
	CustomerMaster
SET
	archiveFlag = @cusUNK
WHERE
	customerUNK like @cusUNK
GO

CREATE PROCEDURE DeleteInvoice (@invoiceUNK varchar(36))
AS

UPDATE
	InvoiceTempl
SET
	archiveFlag = @invoiceUNK
WHERE
	invoiceUNK like @invoiceUNK
GO

CREATE PROCEDURE DeleteMngUnit (@mngUNK varchar(36))
AS

UPDATE
	MngUnit
SET
	archiveFlag = @mngUNK
WHERE
	mngUNK like @mngUNK
GO

CREATE PROCEDURE DeletePackage (@packageUNK varchar(36))
AS

UPDATE
	PackageMaster
SET
	archiveFlag = @packageUNK
WHERE
	packageUNK like @packageUNK
GO

CREATE PROCEDURE DeleteProduct (@productUNK varchar(36))
AS

UPDATE
	ProductMaster
SET
	archiveFlag = @productUNK
WHERE
	productUNK like @productUNK
GO

CREATE PROCEDURE DeleteReg (@regUNK varchar(36))
AS

UPDATE
	RegMaster
SET
	archiveFlag = @regUNK
WHERE
	regUNK like @regUNK
GO

CREATE PROCEDURE DeleteStaff (@staffUNK varchar(36))
AS

UPDATE
	StaffMaster
SET
	archiveFlag = @staffUNK
WHERE
	staffUNK like @staffUNK
GO

CREATE PROCEDURE DeleteSupplier (@supplierUNK varchar(36))
AS

UPDATE
	SupplierMaster
SET
	archiveFlag = @supplierUNK
WHERE
	supplierUNK like @supplierUNK
GO

CREATE PROCEDURE DeleteTax (@taxUNK varchar(36))
AS

UPDATE
	TaxMaster
SET
	archiveFlag = @taxUNK
WHERE
	taxUNK like @taxUNK
GO

CREATE PROCEDURE DeleteTransaction (@transUNK varchar(36))
AS

UPDATE
	TransactionMaster
SET
	archiveFlag = @transUNK
WHERE
	transactionUNK like @transUNK
GO

CREATE PROCEDURE GetRegByInvoiceTemplate (@invoiceTplUNK varchar(36), @RowCount int output)  
AS 

SELECT 
	*
FROM 
	RegMaster
WHERE 
	RegMaster.invoiceUNK like @invoiceTplUNK and
	(archiveFlag like '' or archiveFlag is null)
GO

CREATE PROCEDURE DeleteInvoiceTemplate (@invoiceTplUNK varchar(36))
AS
UPDATE
	InvoiceTempl
SET
	archiveFlag = 'archived'
WHERE
	invoiceUNK like @invoiceTplUNK
GO

CREATE PROCEDURE UpdateInvoiceTemplate (@invoiceTplUNK varchar(36), 
								@invoiceTplName nvarchar(255), 
								@invoiceTplCode varchar(255), 
								@transType varchar(10))
AS
UPDATE
	InvoiceTempl
SET
	name=@invoiceTplName,
	code=@invoiceTplCode,
	transType = @transType
WHERE
	invoiceUNK like @invoiceTplUNK
GO

CREATE PROCEDURE GetAllUnRegInvoice (@RowCount int output)  
as 

select * from InvoiceTempl left join RegMaster on InvoiceTempl.invoiceUNK = RegMaster.invoiceUNK
	 where  RegMaster.regUNK is null
GO

CREATE PROCEDURE GetAllInvoiceByTransType (@transactionType varchar(10), @RowCount int output)
AS

Select Distinct * FROM
	InvoiceTempl left join RegMaster on InvoiceTempl.invoiceUNK = RegMaster.invoiceUNK
WHERE
	InvoiceTempl.transType = @transactionType And
	RegMaster.regUNK is not null And
	(RegMaster.archiveFlag is null or RegMaster.archiveFlag like '') 
GO

CREATE PROCEDURE UpdateInvoiceRegNumber (@invoiceUNK varchar(36))
AS
Update RegMaster 
Set number = number - 1, archiveFlag = (CASE when (number <= 1) then 'archived' end)
Where invoiceUNK = @invoiceUNK
GO

CREATE PROCEDURE SearchProduct (@textSearch varchar(36), @RowCount int output)
AS

Select Distinct * FROM
	ProductMaster 
WHERE
	proName LIKE @textSearch + '%' And
	(archiveFlag is null or archiveFlag like '') 
GO

CREATE PROCEDURE [dbo].[SearchProductBySupplier] (@textSearch varchar(36), @supplierUNK varchar(36), @RowCount int output)
AS
Select Distinct * FROM
	ProductMaster 
WHERE
	proName LIKE @textSearch + '%' And
	supplierUNK like @supplierUNK And
	(archiveFlag is null or archiveFlag like '') 
GO

CREATE PROCEDURE UpdateAccount (@accCode varchar(36), 
				@tkcAmount float,
				@tknAmount float)
AS

UPDATE 
	AccMaster
SET
	TKCBalance = TKCBalance + @tkcAmount,
	TKNBalance = TKNBalance + @tknAmount
WHERE 
	accCode = @accCode
GO

USE [inhd]
GO

